package service.implement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import model.Project;
import service.AbstractConnection;
import service.Constan;
import service.ProjectAPI;

public class ProjectDAO extends AbstractConnection implements ProjectAPI{
//	private Connection conn=dataConnection();
	@Override
	public int addProject(Project pj) {
		String query="insert into project values(?,?,?,?)";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, java.sql.Types.NULL);
			ps.setString(2, pj.getNameProject());
			ps.setString(3, pj.getStartDate());
			ps.setString(4, pj.getEndDate());
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();		
		} 
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}

	@Override
	public int updateProject(Project pj) {
		String query="update project set nameProject=?, startDate=?,endDate=? where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, pj.getNameProject());
			ps.setString(2, pj.getStartDate());
			ps.setString(3,pj.getEndDate());
			ps.setInt(4, pj.getId());
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}

	@Override
	public int deleteProject(int id) {
		String query="delete from project where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}

	@Override
	public boolean checkName(String name) {
		String query="Select * FROM project WHERE nameproject=?";
		try{
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return false;
	}

	@Override
	public ArrayList<Project> getProject() {
		ArrayList<Project> dspj = new ArrayList<>();	
		String query = "SELECT * FROM project";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt(1);
				String nameProject = rs.getString(2);
				String startDate = rs.getString(3);
				String endDate=rs.getString(4);
				Project pj = new Project(id,nameProject,startDate,endDate);
				dspj.add(pj);
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return dspj;
	}

	@Override
	public Project getProjectById(int id) {
		String query = "SELECT * FROM project where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id1 = rs.getInt(1);
				String nameProject = rs.getString(2);
				String startDate=rs.getString(3);
				String endDate=rs.getString(4);
				Project pj=new Project(id,nameProject,startDate,endDate);
				return pj;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			closeConnection();
		}
		return null;
	}

	@Override
	public ArrayList<Project> getProjectByName(String name) {
		ArrayList<Project> list = new ArrayList<>();
		String query = "SELECT * FROM project where nameProject like ?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, "%" + name + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String nameProject1=rs.getString(2);
				String startDate = rs.getString(3);
				String endDate = rs.getString(4);
				Project pj = new Project(id, nameProject1, startDate,endDate);
				list.add(pj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			closeConnection();
		}
		return list;
	}
}
